package com.jason.practice.utils;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import com.jason.practice.model.CitizenLoanInfo;
import com.jason.practice.model.FinanceServiceInfo;
import com.jason.practice.model.InsuranceProductInfo;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.List;

/**
 * ExcelUtil 工具类
 * @author ZYGisComputer
 */
public class ExcelUtil {

    private static final String DIAN = ".";

    private static final String XLS = "xls";

    private static final String XLSX = "xlsx";

    public static <T> List<T> parseExcel(File file, Class<?> clazz, ImportParams im) throws Exception {
        if(checkIsExcel(file)){
            return ExcelImportUtil.importExcel(file, clazz, im);
        }
        throw new Exception("文件格式错误!");

    }

    private static boolean checkIsExcel(File file){
        if (null != file) {
            String fileName = file.getName();
            if(!fileName.contains(DIAN)){
                return false;
            }
            String type = fileName.substring(fileName.lastIndexOf(".") + 1);
            return XLS.equalsIgnoreCase(type) || XLSX.equalsIgnoreCase(type);
        }
        throw new NullPointerException("文件为空");
    }

    public static void editExcel(File file, CitizenLoanInfo total) throws IOException {
        String path = file.getAbsolutePath();
        FileInputStream fileInputStream = new FileInputStream(path);
        Workbook excel = WorkbookFactory.create(fileInputStream);
        Sheet sheet = excel.getSheetAt(0);
        System.out.println(sheet.getSheetName());
        CellStyle cellStyle = excel.createCellStyle();
        Font font = excel.createFont();
        short sh = 14;
        font.setFontHeightInPoints(sh);
        font.setFontName("宋体");
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//设置水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//设置垂直居中
        cellStyle.setFont(font);
        cellStyle.setBorderBottom(BorderStyle.THIN);//左边框
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBorderTop(BorderStyle.THIN); //上边框
        cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBorderRight(BorderStyle.THIN);//右边框
        cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());

        sheet.getRow(11).getCell(2).setCellValue(total.getCreditLoanNum());
        sheet.getRow(11).getCell(3).setCellValue(total.getCreditLine());
        sheet.getRow(11).getCell(4).setCellValue(total.getLoanBalance());
        sheet.getRow(11).getCell(5).setCellValue(total.getAccGrantLoan());
        sheet.getRow(11).getCell(6).setCellValue(total.getWorkNum());

        sheet.getRow(11).getCell(2).setCellStyle(cellStyle);
        sheet.getRow(11).getCell(3).setCellStyle(cellStyle);
        sheet.getRow(11).getCell(4).setCellStyle(cellStyle);
        sheet.getRow(11).getCell(5).setCellStyle(cellStyle);
        sheet.getRow(11).getCell(6).setCellStyle(cellStyle);

        FileOutputStream fileOutputStream = new FileOutputStream(path);
        excel.write(fileOutputStream);
        fileInputStream.close();
        fileOutputStream.close();
        excel.close();
    }

    public static void editExcel(File file, FinanceServiceInfo total) throws IOException {
        String path = file.getAbsolutePath();
        FileInputStream fileInputStream = new FileInputStream(path);
        Workbook excel = WorkbookFactory.create(fileInputStream);
        Sheet sheet = excel.getSheetAt(2);
        CellStyle cellStyle = excel.createCellStyle();
        Font font = excel.createFont();
        short sh = 14;
        font.setFontHeightInPoints(sh);
        font.setFontName("宋体");
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//设置水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//设置垂直居中
        cellStyle.setFont(font);
        cellStyle.setBorderBottom(BorderStyle.THIN);//左边框
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBorderTop(BorderStyle.THIN); //上边框
        cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBorderRight(BorderStyle.THIN);//右边框
        cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());

        int row = 8;
        sheet.getRow(row).getCell(2).setCellValue(total.getServiceNum());
        sheet.getRow(row).getCell(3).setCellValue(total.getCoverCustomNum());
        sheet.getRow(row).getCell(4).setCellValue(total.getProfitPrice());

        sheet.getRow(row).getCell(2).setCellStyle(cellStyle);
        sheet.getRow(row).getCell(3).setCellStyle(cellStyle);
        sheet.getRow(row).getCell(4).setCellStyle(cellStyle);

        FileOutputStream fileOutputStream = new FileOutputStream(path);
        excel.write(fileOutputStream);
        fileInputStream.close();
        fileOutputStream.close();
        excel.close();
    }

    public static void editExcel(File file, InsuranceProductInfo total) throws IOException {
        String path = file.getAbsolutePath();
        FileInputStream fileInputStream = new FileInputStream(path);
        Workbook excel = WorkbookFactory.create(fileInputStream);
        Sheet sheet = excel.getSheetAt(1);
        System.out.println(sheet.getSheetName());
        CellStyle cellStyle = excel.createCellStyle();
        Font font = excel.createFont();
        short sh = 14;
        font.setFontHeightInPoints(sh);
        font.setFontName("宋体");
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//设置水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//设置垂直居中
        cellStyle.setFont(font);
        cellStyle.setBorderBottom(BorderStyle.THIN);//左边框
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBorderTop(BorderStyle.THIN); //上边框
        cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBorderRight(BorderStyle.THIN);//右边框
        cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        int insuranceProductNum = 0;
        int teamInsuredEnterpriseNum = 0;
        int teamInsuredHumanNum = 0;
        int insuredHumanNum = 0;
        int accPremiumIncome = 0;
        int insurancePrice = 0;
        int accPayPrice = 0;

        sheet.getRow(12).getCell(2).setCellValue(total.getInsuranceProductNum());
        sheet.getRow(12).getCell(3).setCellValue(total.getTeamInsuredEnterpriseNum());
        sheet.getRow(12).getCell(4).setCellValue(total.getTeamInsuredHumanNum());
        sheet.getRow(12).getCell(5).setCellValue(total.getInsuredHumanNum());
        sheet.getRow(12).getCell(6).setCellValue(total.getAccPremiumIncome());
        sheet.getRow(12).getCell(7).setCellValue(total.getInsurancePrice());
        sheet.getRow(12).getCell(8).setCellValue(total.getAccPayPrice());

        sheet.getRow(12).getCell(2).setCellStyle(cellStyle);
        sheet.getRow(12).getCell(3).setCellStyle(cellStyle);
        sheet.getRow(12).getCell(4).setCellStyle(cellStyle);
        sheet.getRow(12).getCell(5).setCellStyle(cellStyle);
        sheet.getRow(12).getCell(6).setCellStyle(cellStyle);
        sheet.getRow(12).getCell(7).setCellStyle(cellStyle);
        sheet.getRow(12).getCell(8).setCellStyle(cellStyle);


        FileOutputStream fileOutputStream = new FileOutputStream(path);
        excel.write(fileOutputStream);
        fileInputStream.close();
        fileOutputStream.close();
        excel.close();
    }

}